﻿--SELECT *  FROM TBL_CUSTOMER b,[VUDT1].[ACTIVE_CUSTOMER_1].[dbo].[ACTIVE_CUSTOMER_20160831] a
--where a.cif = b.cif

--select * from server12.tranglt9.dbo.active_customer_ptkd
--where yearmonth= '2016-08'
--and tt_KH ='1' 

--select * from TBL_CUSTOMER_WIND_UP order by cif

select distinct cif from TBL_CUSTOMER_WIND_UP where month_check < '201610'

delete from TBL_CUSTOMER_WIND_UP

declare @count int = 4
while @count < 11
begin

declare @month nvarchar(20) = convert(nvarchar(20), @count)
if(@count <10) set @month = '0' + @month
exec('
insert TBL_CUSTOMER_WIND_UP
SELECT ''2016' +@month+''' ,a.cif, a.[Ngày tra cứu thông tin] as date_check_status, a.[Trạng thái hoạt động] as working_status, a.[Ghi chú] as note, b.BUSINESS_DATE,b.CUS_NAME,b.BRANCH_ID,b.CUS_OPEN_DATE,b.INDUSTRY_NAME,b.INDUSTRY_NAME_EN,b.DAO,b.dao_name
 FROM LIST_CUSTOMER_WIND_UP_2016'+@month+' A, TBL_CUSTOMER B
WHERE A.CIF = B.CIF
group by a.cif, a.[Ngày tra cứu thông tin], a.[Trạng thái hoạt động], a.[Ghi chú], b.BUSINESS_DATE,b.CUS_NAME,b.BRANCH_ID,b.CUS_OPEN_DATE,b.INDUSTRY_NAME,b.INDUSTRY_NAME_EN,b.DAO,b.dao_name ')
 set @count = @count +1
end 

--select * from TBL_CUSTOMER where cif in (select cif from TBL_CUSTOMER_WIND_UP)

--SELECT DISTINCT CIF FROM [LIST_CUSTOMER_WIND_UP_201609]

--select * from [LIST_CUSTOMER_WIND_UP_201607]

--delete from [LIST_CUSTOMER_WIND_UP_201609] where stt is null

--select * from TBL_CUSTOMER_WIND_UP order by cif

--select distinct working_status from TBL_CUSTOMER_WIND_UP order by working_status

--drop table TBL_CUSTOMER_WIND_UP

--SELECT '201607' as month_check, a.cif, a.[Ngày tra cứu thông tin] as date_check_status
--, a.[Trạng thái hoạt động] as working_status
--, a.[Ghi chú] as note
--, b.BUSINESS_DATE
--,b.CUS_NAME
--,b.BRANCH_ID
--,b.CUS_OPEN_DATE
--,b.INDUSTRY_NAME
--,b.INDUSTRY_NAME_EN
--,b.DAO
--,b.dao_name into TBL_CUSTOMER_WIND_UP
-- FROM [dbo].[LIST_CUSTOMER_WIND_UP_201607] A, TBL_CUSTOMER B
--WHERE A.CIF = B.CIF
--group by a.cif, a.[Ngày tra cứu thông tin], a.[Trạng thái hoạt động], a.[Ghi chú], b.BUSINESS_DATE
--,b.CUS_NAME
--,b.BRANCH_ID
--,b.CUS_OPEN_DATE
--,b.INDUSTRY_NAME
--,b.INDUSTRY_NAME_EN
--,b.DAO
--,b.dao_name